package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.BookingBO;
import com.junzhiit.nengji.bo.BookingListBO;
import com.junzhiit.nengji.dao.BookingDAO;
import com.junzhiit.nengji.model.Booking;
import com.junzhiit.nengji.model.enums.BookingStatus;

@Repository
public class BookingDAOImpl extends AbstractBaseDAO<Booking> implements BookingDAO {

	@Override
	public Booking getByBookingNumber(String bookingNumber) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select * from  booking where booking_number = ? and is_delete = ?");
		params.setString(bookingNumber);
		params.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(), Booking.class, params);
	}

	@Override
	public List<BookingBO> findAll(Booking booking,Integer pageNumber,Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select b.id,b.choose_time,b.shop_id,b.user_id,b.transaction_number,b.booking_number,b.table_type_id,b.money   ");
		sql.append(" ,b.pay_time,b.pay_way,b.number,b.linkman_name,b.linkman_sex,b.linkman_mobile_phone,b.status,b.booking_type ");
		sql.append(" ,b.remark,b.end_time endTime,tt.name tableTypeName ");
		sql.append(" from  booking b left join table_type tt on b.table_type_id = tt.id ");
		sql.append(" where b.shop_id = ? and b.user_id= ? and b.is_delete = ? ");
		params.setString(booking.getShopId());
		params.setString(booking.getUserId());
		params.setInt(IsDelete.NO.toIntValue());
		if(booking.getBookingType()!=null){
			sql.append(" and b.booking_type = ? ");
			params.setInt(booking.getBookingType());
		}
		if(booking.getStatus()!=null){
			if(BookingStatus.CLOSE.toIntValue() == booking.getStatus()){
				sql.append(" and (b.status = ? or b.end_time < now()) ");
				params.setInt(booking.getStatus());
			}else{
				sql.append(" and b.status = ? and b.end_time >= now() ");
				params.setInt(booking.getStatus());
			}
		}
		sql.append(" order by b.created_time desc ");
		return super.queryForPagingList(sql.toString(), BookingBO.class, params, pageNumber,maxPageRow);
	}
	
	@Override
	public List<BookingBO> findPageAll(BookingListBO bookingListBO,Integer pageNumber,Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select b.*,tt.name tableTypeName from  booking b ");
		sql.append(" left join table_type tt on b.table_type_id = tt.id");
		sql.append(" where b.shop_id = ? and b.is_delete = ? ");
		params.setString(bookingListBO.getShopId());
		params.setInt(IsDelete.NO.toIntValue());
		if(bookingListBO.getBookingType()!=null){
			sql.append(" and booking_type = ? ");
			params.setInt(bookingListBO.getBookingType());
		}
		if(bookingListBO.getStatus()!=null){
			sql.append(" and status = ? ");
			params.setInt(bookingListBO.getStatus());
		}
		if(StringUtils.isNotBlank(bookingListBO.getBookingNumber())){
			sql.append(" and booking_number = ? ");
			params.setString(bookingListBO.getBookingNumber());
		}
		if(StringUtils.isNotBlank(bookingListBO.getTransactionNumber())){
			sql.append(" and transaction_number = ? ");
			params.setString(bookingListBO.getTransactionNumber());
		}
		if(bookingListBO.getStartDate()!=null){
			sql.append(" and b.created_time >= ? ");
			params.setDate(bookingListBO.getStartDate());
		}
		if(bookingListBO.getEndDate()!=null){
			sql.append(" and b.created_time < ? ");
			params.setDate(bookingListBO.getEndDate());
		}
		if(bookingListBO.getBuyStartDate()!=null){
			sql.append(" and b.pay_time >= ? ");
			params.setDate(bookingListBO.getStartDate());
		}
		if(bookingListBO.getBuyEndDate()!=null){
			sql.append(" and b.pay_time < ? ");
			params.setDate(bookingListBO.getBuyEndDate());
		}
		
		sql.append(" order by b.created_time desc ");
		return super.queryForPagingList(sql.toString(), BookingBO.class, params, pageNumber,maxPageRow);
	}
	
	@Override
	public int getTotalCount(BookingListBO bookingListBO) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select count(*) from  booking b ");
		sql.append(" left join table_type tt on b.table_type_id = tt.id");
		sql.append(" where b.shop_id = ? and b.is_delete = ? ");
		params.setString(bookingListBO.getShopId());
		params.setInt(IsDelete.NO.toIntValue());
		if(bookingListBO.getBookingType()!=null){
			sql.append(" and booking_type = ? ");
			params.setInt(bookingListBO.getBookingType());
		}
		if(bookingListBO.getStatus()!=null){
			sql.append(" and status = ? ");
			params.setInt(bookingListBO.getStatus());
		}
		if(StringUtils.isNotBlank(bookingListBO.getBookingNumber())){
			sql.append(" and booking_number = ? ");
			params.setString(bookingListBO.getBookingNumber());
		}
		if(StringUtils.isNotBlank(bookingListBO.getTransactionNumber())){
			sql.append(" and transaction_number = ? ");
			params.setString(bookingListBO.getTransactionNumber());
		}
		if(bookingListBO.getStartDate()!=null){
			sql.append(" and b.created_time >= ? ");
			params.setDate(bookingListBO.getStartDate());
		}
		if(bookingListBO.getEndDate()!=null){
			sql.append(" and b.created_time < ? ");
			params.setDate(bookingListBO.getEndDate());
		}
		if(bookingListBO.getBuyStartDate()!=null){
			sql.append(" and b.pay_time >= ? ");
			params.setDate(bookingListBO.getStartDate());
		}
		if(bookingListBO.getBuyEndDate()!=null){
			sql.append(" and b.pay_time < ? ");
			params.setDate(bookingListBO.getBuyEndDate());
		}
		
		return super.queryForInt(sql.toString(), params);
	}
	
	@Override
	public BookingBO findById(String id){
		StringBuffer sql = new StringBuffer();
		sql.append(" select b.*,tt.name tableTypeName from  booking b ");
		sql.append(" left join table_type tt on b.table_type_id = tt.id");
		sql.append(" where b.id=? ");
		
		StatementParameter params=new StatementParameter();
		params.setString(id);
		
		return super.query(sql.toString(), BookingBO.class,params);
		
	}
	
	@Override
	public int getPayCount(String shopId){
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(*) from booking");
		sql.append(" where is_delete=?");
		sql.append(" and shop_id=?");
	 	
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopId);
		
		return super.queryForInt(sql.toString(),param);
	}
		
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public Boolean updateStatusByValidTime(){
		StringBuffer sql=new StringBuffer();
		sql.append(" update booking set status = ?");
		sql.append(" where is_delete=?");
		sql.append(" and end_time <= now()");
		sql.append(" and status = ?");
		
		StatementParameter param=new StatementParameter();
		param.setInt(BookingStatus.CLOSE.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(BookingStatus.CONFIRM.toIntValue());
		
		return super.updateForBoolean(sql.toString(), param);
	}
	
}
